-- Demotes the hierarchy branch rooted at the hierarchyKey sent in to be the last child of
-- its older sibling. Since it is already in the right place as far as sort order goes, this
-- just means incrementing the depth on all descendants and changing the parent of the one sent
CREATE PROCEDURE [dbo].[asi_HierarchyDemoteBranch] @hierarchyKey uniqueidentifier AS
DECLARE
    @NewParentSort int, @Depth int, @LowSort int, @HighSort int, @RootHierarchyKey uniqueidentifier
BEGIN
    -- find the prior sibling that will become the parent
    SELECT @RootHierarchyKey = a.RootHierarchyKey, @Depth=a.Depth, @LowSort = a.SortOrder, @NewParentSort = ISNULL(Max(b.SortOrder),0)
      FROM Hierarchy a LEFT OUTER JOIN Hierarchy b
            ON a.RootHierarchyKey = b.RootHierarchyKey
           AND a.SortOrder > b.SortOrder
       AND a.Depth = b.Depth
       AND a.ParentHierarchyKey = b.ParentHierarchyKey
     WHERE a.HierarchyKey = @hierarchyKey
     GROUP BY a.RootHierarchyKey, a.Depth, a.SortOrder

    -- if we didn't get one (shouldn't happen) throw an exception
    if @NewParentSort IS NULL
        RAISERROR ('Cannot Demote. No older sibling available',16,1)
    
    -- find the high sortorder to move (if it is there) as the lowest sort order that is higher than the one sent in
    -- and with a depth equal or shallower
    SELECT @HighSort = Min(SortOrder)
      FROM Hierarchy
     WHERE RootHierarchyKey = RootHierarchyKey
       AND SortOrder > @LowSort
       AND Depth <= @Depth
    
    -- if we found the high sort, update everything in between
    IF @HighSort IS NOT NULL
    BEGIN
        UPDATE Hierarchy
           SET Depth = Depth + 1
         WHERE RootHierarchyKey = @RootHierarchyKey
           AND SortOrder >= @LowSort
           AND SortOrder < @HighSort
    END
    -- if we didn't, update everything beyond this one
    ELSE
    BEGIN
        UPDATE Hierarchy
           SET Depth = Depth + 1
         WHERE RootHierarchyKey = @RootHierarchyKey
           AND SortOrder >= @LowSort
    END

    -- move to the new parent
    UPDATE Hierarchy
       SET ParentHierarchyKey = (
        SELECT HierarchyKey
          FROM Hierarchy
         WHERE RootHierarchyKey = @RootHierarchyKey
           AND SortOrder = @NewParentSort)
     WHERE HierarchyKey = @hierarchyKey
    
END

GO